package com.yidu.BusinessDealWith.mapper;

import com.yidu.BusinessDealWith.entity.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface InventoryMapper {
    //证券应收应付库存统计
    @Select("SELECT IFNULL(siy.securityid,sad.securityid) AS securityid,IFNULL(sad.money,0)+IFNULL(siy.securityTotalAmount,0) AS securityTotalAmount,IFNULL(siy.businessStatu,sad.moneyFlow) AS businessStatu,IFNULL(siy.securityName,sad.securityName) AS securityName,ifnull(siy.cashId,sad.cashId) as cashId,securityInventoryId FROM \n" +
            "(SELECT * FROM securityInventory WHERE fundid = #{fundId} and securityType = 3 AND securityInventoryTime =  DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) AS siy \n" +
            "LEFT JOIN (SELECT sd.securityid AS securityid,IFNULL(money,0)*moneyFlow AS money,moneyFlow,cashId,sy.securityName AS securityName FROM securityAccrued AS sd JOIN SECURITY AS sy ON sy.securityId = sd.securityId WHERE  businessType= 3 AND securityDate = #{dateTime}) AS sad ON sad.securityId=siy.securityId \n" +
            "UNION\n" +
            "SELECT IFNULL(siy.securityid,sad.securityid) AS securityid,IFNULL(sad.money,0)+IFNULL(siy.securityTotalAmount,0) AS securityTotalAmount,IFNULL(siy.businessStatu,sad.moneyFlow) AS businessStatu,IFNULL(siy.securityName,sad.securityName) AS securityName,IFNULL(siy.cashId,sad.cashId) AS cashId,securityInventoryId FROM \n" +
            "(SELECT * FROM securityInventory WHERE fundid = #{fundId} and securityType = 3 AND securityInventoryTime =  DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) AS siy \n" +
            "RIGHT JOIN (SELECT sd.securityid AS securityid,IFNULL(money,0)*moneyFlow AS money,moneyFlow,cashId,sy.securityName AS securityName FROM securityAccrued AS sd JOIN SECURITY AS sy ON sy.securityId = sd.securityId WHERE businessType = 3 AND securityDate = #{dateTime}) AS sad ON sad.securityId=siy.securityId \n" +
            "\n")
    public List<InventorySecurityInventoryEntity> selectz(String fundId, String dateTime);
    //现金应收应付库存统计
    @Select("SELECT IFNULL(ciy.cashId,cad.cashId) AS cashId,(IFNULL(ciy.cashTotalAmount,0)+IFNULL(cad.moneySun,0)) AS cashTotalAmount,IFNULL(ciy.businessStatu,cad.moneyFlow) AS businessStatu,IFNULL(ciy.businessType,cad.businessType) AS businessType,cashInventoryId  FROM ( SELECT * FROM cashInventory WHERE fundid = #{fundId} and businessType = 3 AND cashInventoryTime = DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) AS ciy \n" +
            "LEFT JOIN ( SELECT IFNULL(moneySun,0)*moneyFlow AS moneySun,cashId,moneyFlow,businessType FROM cashaccrued WHERE cashDate= #{dateTime}) AS cad ON ciy.cashId = cad.cashId\n" +
            "UNION\n" +
            "SELECT IFNULL(ciy.cashId,cad.cashId) AS cashId,(IFNULL(ciy.cashTotalAmount,0)+IFNULL(cad.moneySun,0))  AS cashTotalAmount,IFNULL(ciy.businessStatu,cad.moneyFlow) AS businessStatu,IFNULL(ciy.businessType,cad.businessType) AS businessType,cashInventoryId  FROM ( SELECT * FROM cashInventory WHERE fundid = #{fundId} and businessType = 3 AND cashInventoryTime = DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) AS ciy \n" +
            "RIGHT JOIN ( SELECT IFNULL(moneySun,0)*moneyFlow AS moneySun,cashId,moneyFlow,businessType FROM cashaccrued WHERE cashDate= #{dateTime}) AS cad ON ciy.cashId = cad.cashId\n")
    public List<InventoryCashInventoryEntity> selectx(String fundId, String dateTime);
    //现金库存统计
    @Select("SELECT IFNULL(cis.balance,0)+IFNULL(mae.allocateMoney,0) AS balance , IFNULL(cis.cashId,mae.cashId) AS cashId,IFNULL(cis.accounName,mae.accounName) AS accounName,cashInventorysId  FROM (SELECT * FROM cashInventorys WHERE fundid=#{fundId} and cashInventorysTime = DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) cis\n" +
            "LEFT JOIN (SELECT ch.fundId as fundId,(IFNULL(allocateMoney,0)*allocateFlow) AS allocateMoney ,me.cashId AS cashId,ch.accounName AS accounName FROM moneyAllocate  me JOIN cash  ch ON me.cashId = ch.cashId WHERE allocateDate = #{dateTime}) mae ON cis.cashId= mae.cashId\n" +
            "UNION\n" +
            "SELECT IFNULL(cis.balance,0)+IFNULL(mae.allocateMoney,0) AS balance , IFNULL(cis.cashId,mae.cashId) AS cashId,IFNULL(cis.accounName,mae.accounName) AS accounName,cashInventorysId  FROM (SELECT * FROM cashInventorys WHERE fundid=#{fundId} and cashInventorysTime = DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) cis\n" +
            "RIGHT JOIN (SELECT ch.fundId as fundId,(IFNULL(allocateMoney,0)*allocateFlow) AS allocateMoney ,me.cashId AS cashId,ch.accounName AS accounName FROM moneyAllocate  me JOIN cash  ch ON me.cashId = ch.cashId WHERE allocateDate = #{dateTime}) mae ON cis.cashId= mae.cashId\n")
    public List<InventoryCashInventorysEntity> selectxk(String fundId, String dateTime);
    //证券库存统计
    @Select("SELECT IFNULL(sis.securityId,tae.securityId) AS securityId,IFNULL(sis.securityMoney,0)+IFNULL(tae.tradeMoney,0) AS securityMoney,IFNULL(sis.securityNum,0)+ifnull(tae.tradeNum,0) AS securityNum,(IFNULL(CASE WHEN tae.tradeNum >0 THEN (IFNULL(sis.securityMoney,0)+tae.tradeMoney)/(IFNULL(sis.securityNum,0)+tae.tradeNum) ELSE sis.securityPrice END,tae.tradePrice)) AS securityPrice,securityInventorsId\n" +
            "FROM (SELECT * FROM securityInventors WHERE fundid = #{fundId} AND securityInventorsTime=DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) AS sis\n" +
            "LEFT JOIN (SELECT (CASE WHEN tradeType = 2 THEN IFNULL(tradePrice,0)*IFNULL(tradeNum,0)*-1 ELSE IFNULL(tradeMoney,0)*1 END) AS tradeMoney,securityId,(CASE WHEN tradeType = 2 THEN IFNULL(tradeNum,0)*-1 ELSE IFNULL(tradeNum,0)*1 END) AS tradeNum,tradePrice FROM trade WHERE tradeDate = #{dateTime}) AS tae ON sis.securityId = tae.securityId \n" +
            "UNION\n" +
            "SELECT IFNULL(sis.securityId,tae.securityId) AS securityId,IFNULL(sis.securityMoney,0)+IFNULL(tae.tradeMoney,0)  AS securityMoney,IFNULL(sis.securityNum,0)+ifnull(tae.tradeNum,0) AS securityNum,(IFNULL(CASE WHEN tae.tradeNum >0 THEN (IFNULL(sis.securityMoney,0)+tae.tradeMoney)/(IFNULL(sis.securityNum,0)+tae.tradeNum) ELSE sis.securityPrice END,tae.tradePrice)) AS securityPrice,securityInventorsId\n" +
            "FROM (SELECT * FROM securityInventors where fundid = #{fundId} and securityInventorsTime=DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) AS sis\n" +
            "RIGHT JOIN (SELECT (CASE WHEN tradeType = 2 THEN IFNULL(tradePrice,0)*IFNULL(tradeNum,0)*-1 ELSE IFNULL(tradeMoney,0)*1 END) AS tradeMoney,securityId,(CASE WHEN tradeType = 2 THEN IFNULL(tradeNum,0)*-1 ELSE IFNULL(tradeNum,0)*1 END) AS tradeNum,tradePrice FROM trade WHERE tradeDate = #{dateTime}) AS tae ON sis.securityId = tae.securityId \n")
    public List<InventorySecurityInventorysEntity> selectzk(String fundId, String dateTime);
    //Ta库存统计
    @Select("SELECT IFNULL(ty.fundId,ts.fundId ) AS fundId,IFNULL(ty.taMoney,0)+IFNULL(ts.totalMoney,0) AS taMoney, IFNULL(ty.number,0)+IFNULL(businessNum,0) AS number,taInventoryId FROM (SELECT * FROM TAInventory WHERE fundid = #{fundId} and statisticsDate= DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d')) ty \n" +
            "LEFT JOIN (SELECT totalMoney*(CASE businessType WHEN 2 THEN -1 ELSE 1 END) AS totalMoney,businessNum*(CASE businessType WHEN 2 THEN -1 ELSE 1 END) AS businessNum,fundId FROM TABusiness WHERE businessTime\t=#{dateTime}) ts ON ty.fundId = ts.fundId\n" +
            "UNION \n" +
            "SELECT IFNULL(ty.fundId,ts.fundId ) AS fundId,IFNULL(ty.taMoney,0)+IFNULL(ts.totalMoney,0) AS taMoney, IFNULL(ty.number,0)+IFNULL(businessNum,0) AS number,taInventoryId  FROM (SELECT * FROM TAInventory WHERE fundid = #{fundId} and statisticsDate=DATE_FORMAT(DATE_ADD(STR_TO_DATE(#{dateTime},'%Y-%m-%d'),INTERVAL -1 DAY),'%Y-%m-%d') ) ty \n" +
            "RIGHT JOIN (SELECT totalMoney*(CASE businessType WHEN 2 THEN -1 ELSE 1 END) AS totalMoney,businessNum*(CASE businessType WHEN 2 THEN -1 ELSE 1 END) AS businessNum,fundId FROM TABusiness WHERE businessTime\t=#{dateTime}) ts ON ty.fundId = ts.fundId\n")
    public List<InventoryTaEntity> selectt(String fundId, String dateTime);
}
